<?php

namespace Ladenise\EveStationTradingBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;

class DefaultController extends Controller {

    public function indexAction($page) {
        
        $maxResults = 10;
        $firstResult = $page * $maxResults;
        
        $entityManagerEve = $this->get('doctrine')->getManager();
        $entityManagerEveStationTrading = $this->get('doctrine')->getManager('eve_station_trading');

        $qb = $entityManagerEve->createQueryBuilder()
                ->select('count(t.id)')
                ->from('LadeniseEveBundle:Invtypes', 't')
                ->innerJoin('LadeniseEveStationTradingBundle:MarketStatRequestInvtype', 'ri', 'WITH', 't.id = ri.typeid')
                ->innerJoin('ri.marketstatresponse', 'r')
                ->where('t.marketgroupid IS NOT NULL')
                ->andWhere('r.all_volume > 1000')
                ->andWhere('r.net_margin_percentage > 30');

        $countInvTypes = $qb->getQuery()->getSingleScalarResult();
        $pages = ceil($countInvTypes / $maxResults);
        
        $dql = "
            SELECT t.id, t.typename, r.buy_max, r.sell_min, r.all_volume, r.net_margin, r.net_margin_percentage 
            FROM LadeniseEveBundle:Invtypes t 
            INNER JOIN LadeniseEveStationTradingBundle:MarketStatRequestInvtype ri WITH t.id = ri.typeid 
            INNER JOIN ri.marketstatresponse r 
            WHERE t.marketgroupid IS NOT NULL
            AND r.net_margin_percentage > 30
            AND r.all_volume > 1000
            ORDER BY r.net_margin DESC
            ";

        $query = $entityManagerEve->createQuery($dql)
        ->setFirstResult($firstResult)
        ->setMaxResults($maxResults);

        $invtypes = $query->getResult();

        return $this->render('LadeniseEveStationTradingBundle:Default:index.html.twig', array('invtypes' => $invtypes, 'page' => $page, 'pages' => $pages));
    }

}
